#include "StdAfx.h"
#include "TreeTable.h"

TreeTable::TreeTable(fuAdoConnector & dbcon)
:m_dbcon(dbcon)
{
}

TreeTable::~TreeTable(void)
{
}

bool TreeTable::getTreeNodes( CString & node_name )
{
    CString table_name = _T("nested_category");
    CString cmd;
    cmd.Format(
        _T("SELECT node.*                                      ")
        _T("FROM %s node, %s parent                            ")
        _T("WHERE node.lft BETWEEN parent.lft AND parent.rgt   ")
        _T("  AND parent.name = '%s'                           ")
        _T("ORDER BY node.lft                                  ")
        ,table_name,table_name,node_name);

    return false;
}

void TreeTable::getTreeChild( CString & node_name )
{
    CString table_name = _T("nested_category");
    CString cmd;
    cmd.Format(
        _T("SELECT V.*                                                                     ")
        _T("FROM %s T,(                                                                    ")
        _T("   SELECT node.name, (COUNT(parent.name) - (AVG(sub_tree.depth) + 1)) depth    ")
        _T("   FROM %s node,%s parent,%s sub_parent,(                                      ")
        _T("       SELECT V.*                                                              ")
        _T("       FROM %s T,(                                                             ")
        _T("           SELECT node.name, (COUNT(parent.name) - 1) depth                    ")
        _T("           FROM %s node, %s parent                                             ")
        _T("           WHERE node.lft BETWEEN parent.lft AND parent.rgt                    ")
        _T("             AND node.name = '%s'                                              ")
        _T("           GROUP BY node.name) V                                               ")
        _T("       WHERE V.name = T.name                                                   ")
        _T("       ORDER BY T.lft) sub_tree                                                ")
        _T("   WHERE node.lft BETWEEN parent.lft AND parent.rgt                            ")
        _T("     AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt                    ")
        _T("     AND sub_parent.name = sub_tree.name                                       ")
        _T("   GROUP BY node.name) V                                                       ")
        _T("WHERE V.name = T.name AND V.depth <= 1 AND V.depth > 0                         ")
        _T("ORDER BY T.lft                                                                 ")
        ,table_name,table_name,table_name,table_name,table_name,table_name,table_name,node_name);
}

void TreeTable::getNodePath( CString & node_name )
{
    CString table_name = _T("nested_category");
    CString cmd;
    cmd.Format(
        _T("SELECT parent.*                                    ")
        _T("FROM %s node, %s parent                            ")
        _T("WHERE node.lft BETWEEN parent.lft AND parent.rgt   ")
        _T("  AND node.name = '%s'                             ")
        _T("ORDER BY node.lft                                  ")
        ,table_name,table_name,node_name);
}

void TreeTable::getTreeDepth( void )
{
    CString table_name = _T("nested_category");
    CString cmd;
    cmd.Format(
        _T("SELECT V.*                                         ")
        _T("FROM %s T,(                                        ")
        _T("   SELECT node.name, (COUNT(parent.name) - 1) depth")
        _T("   FROM %s node, %s parent                         ")
        _T("   WHERE node.lft BETWEEN parent.lft AND parent.rgt")
        _T("   GROUP BY node.name) V                           ")
        _T("WHERE V.name = T.name                              ")
        _T("ORDER BY T.lft                                     ")
        ,table_name,table_name,table_name);
}

void TreeTable::getNodeDepth( CString & node_name )
{
    CString table_name = _T("nested_category");
    CString cmd;
    cmd.Format(
        _T("SELECT V.*                                                                 ")
        _T("FROM %s T,(																")
        _T("   SELECT node.name,(COUNT(parent.name) - (AVG(sub_tree.depth) + 1)) depth ")
        _T("   FROM %s node,%s parent,%s sub_parent,(									")
        _T("       SELECT V.*                                                          ")
        _T("       FROM %s T,(															")
        _T("           SELECT node.name, (COUNT(parent.name) - 1) depth                ")
        _T("           FROM %s node, %s parent											")
        _T("           WHERE node.lft BETWEEN parent.lft AND parent.rgt                ")
        _T("             AND node.name = '%s'											")
        _T("           GROUP BY node.name) V                                           ")
        _T("       WHERE V.name = T.name                                               ")
        _T("       ORDER BY T.lft) sub_tree                                            ")
        _T("   WHERE node.lft BETWEEN parent.lft AND parent.rgt                        ")
        _T("     AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt                ")
        _T("     AND sub_parent.name = sub_tree.name                                   ")
        _T("   GROUP BY node.name) V                                                   ")
        _T("WHERE V.name = T.name                                                      ")
        _T("ORDER BY T.lft                                                             ")
        ,table_name,table_name,table_name,table_name,table_name,table_name,table_name,node_name);
}

void TreeTable::getLeafNodes( void )
{
    CString table_name = _T("nested_category");
    CString cmd;
    cmd.Format(
        _T("SELECT * FROM %s WHERE rgt = lft + 1")
        ,table_name);
}

void TreeTable::addNodeAfter( CString & node_name, CString & node_name_new )
{
    CString table_name = _T("nested_category");
    CString cmd;
    cmd.Format(
        _T("LOCK TABLE %s WRITE;")
        _T("SELECT @myRight := rgt FROM %s WHERE name = '%s';  ")
        _T("UPDATE %s SET rgt = rgt + 2 WHERE rgt > @myRight;  ")
        _T("UPDATE %s SET lft = lft + 2 WHERE lft > @myRight;  ")
        _T("INSERT INTO %s (name, lft, rgt)                    ")
        _T("VALUES ('%s', @myRight + 1, @myRight + 2);         ")
        _T("UNLOCK TABLES;                                     ")
        ,table_name,table_name,node_name,table_name,table_name,table_name,node_name_new);
}

void TreeTable::delNode( CString & node_name )
{
    CString table_name = _T("nested_category");
    CString cmd;
    cmd.Format(
        _T("LOCK TABLE %s WRITE;                                               ")
        _T("SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1  ")
        _T("FROM %s WHERE name = '%s';                                         ")
        _T("DELETE FROM %s WHERE lft BETWEEN @myLeft AND @myRight;             ")
        _T("UPDATE %s SET rgt = rgt - @myWidth WHERE rgt > @myRight;           ")
        _T("UPDATE %s SET lft = lft - @myWidth WHERE lft > @myRight;           ")
        _T("UNLOCK TABLES;                                                     ")
        ,table_name,table_name,node_name,table_name,table_name,table_name);
}
